"""
@Time    : 2025/7/23 15:39
@Author  : white.tie
@File    : sql_api.py
@Desc    : 
"""
import random
import uvicorn
from sqlalchemy import create_engine, Column, Integer, String, JSON
from sqlalchemy.orm import sessionmaker, declarative_base
from fastapi import FastAPI
# MySQL 数据库连接配置
# SQLALCHEMY_DATABASE_URL = "mysql+mysqlconnector://spider:zzwl%402024@192.168.0.103:3307/spider"
SQLALCHEMY_DATABASE_URL = "mysql+pymysql://root:zzwl%402024@192.168.0.103:3307/spider"
#
#  table 名称
# ware_price_202506
# 创建数据库引擎
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"charset": "utf8mb4"})
print(engine)
try:
    connection = engine.connect()
    connection.close()
    print("连接测试成功！")
except Exception as e:
    print("连接测试失败:", str(e))

# 创建Session类
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 定义基类
Base = declarative_base()
class spiderCookies(Base):
    __tablename__ = "ware_price_202506"  # 对应数据库中的表名
    id = Column(Integer, primary_key=True, index=True)
    model = Column(String, index=True)
    upset_price = Column(String)
    quality_desc = Column(JSON)
    ram = Column(String) # 运行内存
    color = Column(String) # 颜色
    storage = Column(String) # 手机存储
    all_img_url = Column(String) # 图片链接,如果有多个会以 | 隔开  <----
    brand = Column(String) # 品牌名称
    purchase_channel = Column(String)
    upset_price = Column(String) # 成交价
    quality_desc = Column(JSON) # 瑕疵项,json格式
    stick_code = Column(String)  # 验机码
    authorization = Column(String)
    network = Column(String) # 网络制式
    raw_all_img_url = Column(String) # 全部图片链接
    raw_defect_url = Column(JSON) # 原始缺陷图,格式:{"外观***":"https://***.jpg"}
    grade = Column(String) # 等级
    patch = Column(String) # 批次号,分为上午和下午场
    creator = Column(String)  # 创建人
    create_time = Column(String)  # 创建时间
    warranty_period = Column(String) # 保修时长
    info_id = Column(String) # 用于出价请求码
    screen = Column(String)  # 屏幕
    shuck = Column(String)  # 壳
    second_sources = Column(String)  # 第二来源
    category = Column(String)  # 分类场次,如手机专场,数码专场



app = FastAPI()
@app.get("/spider/{page_id}")
async def spider(page_id: int):
    # 创建数据库会话
    db = SessionLocal()
    try:
        # 获取记录数
        # count = db.query(spiderCookies).count()
        # print(count)
        # if count == 0:
        #     return JSONResponse(status_code=404, content={"message": "No data found"})
        # 随机选择记录
        # random_offset = random.randint(0, 5)
        result = db.query(spiderCookies).offset(page_id*10).limit(10).all()
        return result
    finally:
        db.close()

if __name__ == '__main__':
    uvicorn.run(app, host="0.0.0.0",port=8060)